package com.jdbc;

import com.jdbc.util.ExportUtil;
import com.jdbc.util.XLSExportUtil;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.*;

public class JDBCTest {
    public static void main(String[] args) throws Exception {
        //mysqlInfo();
        //sqlserverInfo();
        List<String> rowlist = new ArrayList<>();
        rowlist.add("第一列");
        rowlist.add("第二列");
        rowlist.add("第三列");
        List<Map<String,Object>> dataList = new ArrayList<>();
        for(int i = 0; i < 4; i++){
            Map<String,Object> map = new HashMap<>();
            map.put("one","one");
            map.put("two","one");
            map.put("three","three");
            dataList.add(map);
        }
        combinationList(rowlist,dataList);
    }

    private static void mysqlInfo() throws Exception {
        Map<String,Object> tableMap = new HashMap<>();
        Properties properties = new Properties();
        properties.setProperty("remarks", "true");
        properties.setProperty("useInformationSchema", "true");
        properties.setProperty("user","root");
        properties.setProperty("password","wumengya");
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/liqing?characterEncoding=utf8";
        tableAndColumnINfo(tableMap,url,properties);
    }
    private static void sqlserverInfo() throws Exception {
        Map<String,Object> tableMap = new HashMap<>();
        Properties properties = new Properties();
        properties.setProperty("remarks", "true");
        properties.setProperty("useInformationSchema", "true");
        properties.setProperty("user","fanglh10");
        properties.setProperty("password","fanglhzz(0379)");
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "";
        tableAndColumnINfo(tableMap,url,properties);
    }

    //list遍历
    private static void tableAndColumnINfo(Map<String, Object> tableMap, String url,Properties properties) throws Exception {
        Connection con = DriverManager.getConnection(url,properties);
        DatabaseMetaData dm = con.getMetaData();
        //获得当前数据库的所有表名
        ResultSet resultSet = dm.getTables(null,"%","%",new String[]{"TABLE"});
        while (resultSet.next()){
            String tablename = resultSet.getString("TABLE_NAME");
            String tableremarks = resultSet.getString("REMARKS");
            tableMap.put(tablename,tableremarks);
            System.out.println(tablename);

        }

        List<Map<String,Object>> tableinfoList = new ArrayList<Map<String,Object>>();
        String columnName;
        String columnType;
        for(String table :tableMap.keySet()){
            ResultSet rs = dm.getColumns(null,"%",table,"%");
            System.out.println("==================================================="+table+"====================================================");
            System.out.println(table +" "+tableMap.get(table));
            while (rs.next()){
                Map<String,Object> tableinfo = new HashMap<>();
                tableinfo.put("table",table);
                tableinfo.put("tableremark",tableMap.get(table));
                columnName = rs.getString("COLUMN_NAME");
                columnType = rs.getString("TYPE_NAME");
                int datasize = rs.getInt("COLUMN_SIZE");
                int digits = rs.getInt("DECIMAL_DIGITS"); //小数部分的位数
                int nullable = rs.getInt("NULLABLE");//是否为空，1是 0否
                String remarks = rs.getString("REMARKS");//注解
                String defaulf = rs.getString("COLUMN_DEF"); //默认值
                tableinfo.put("columnName",rs.getString("COLUMN_NAME"));
                tableinfo.put("columnType",rs.getString("TYPE_NAME")+ " " +datasize);
                tableinfo.put("nullable",rs.getInt("NULLABLE"));
                tableinfo.put("remarks",rs.getString("REMARKS"));
                tableinfo.put("defaulf",rs.getString("COLUMN_DEF"));
                tableinfoList.add(tableinfo);
                System.out.println(columnName+" "+columnType+" "+datasize+" "+digits+" "+ nullable+" "+remarks+" "+defaulf);
            }
            System.out.println("=======================================================================================================");
        }
        System.out.println(tableinfoList.toString());
        // 模板路径和输出流
     /*   String templatePath ="C:\\jxtl\\数据库模板.xlsx";
        OutputStream os = new FileOutputStream("C:\\jxtl\\数据库字典.xlsx");
        ExportUtil.exportExcel(templatePath, os, tableinfoList);*/
      /*  String templatePath ="excel/数据库模板.xls";
        String os ="excel/数据库字典.xls";*/
      //不能已xlsx结尾，否则报错
        String templatePath ="C:\\jxtl\\数据库模板.xls";
        String os ="数据库字典.xls";
       XLSExportUtil xlsExportUtil= new XLSExportUtil();
       xlsExportUtil.createExcel(templatePath, tableinfoList, os);
    }

    //组合遍历
    private static void combinationList(List<String> rowlist,List<Map<String,Object>> datalist){
        String templatePath ="C:\\jxtl\\组合遍历模板.xls";
        String os ="组合遍历.xls";
        XLSExportUtil xlsExportUtil= new XLSExportUtil();
        xlsExportUtil.createCombinationExcel(templatePath, rowlist,datalist, os);
    }
}

